﻿using FirstTry.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FirstTry.DB
{
    class DBLeased
    {

        DBConnection dbc = new DBConnection();
        DBBattery dbb = new DBBattery();
        DBLocation dbl = new DBLocation();
        DBUser dbu = new DBUser();


        public void addLeased(Leased l)
        {
            string query = "INSERT INTO Leased (userID, batteryID, timeStamp, done) VALUES (@userID, @batteryID, @timeStamp, @done)";
            SqlConnection con = dbc.GetConnection();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@userID", l.u.ID);
            cmd.Parameters.AddWithValue("@batteryID", l.b.ID);
            cmd.Parameters.AddWithValue("@done", l.done);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public Leased getLeasedByID(string ID)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp, done FROM Leased WHERE ID = " + ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            Leased l = new Leased();
            while (dr.Read())
            {
                l.ID = Convert.ToInt32(dr["ID"]);
                
                l.u = dbu.getUserByID(dr["userID"].ToString());
                l.b = dbb.getBattery(dr["batteryId"].ToString());
                l.timeStamp = dr["timeStamp"].ToString();
                l.done = Convert.ToBoolean(dr["done"]);

            }
            return l;

        }

        public List<Leased> getLeasedByUserID(User u)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp, done FROM Leased WHERE userID = " + u.ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Leased> ll = new List<Leased>();
            while (dr.Read())
            {
                Leased l = new Leased();
                l.ID = Convert.ToInt32(dr["ID"]);
                l.u = dbu.getUserByID(dr["userID"].ToString());
                l.b = dbb.getBattery(dr["batteryId"].ToString());
                l.timeStamp = dr["timeStamp"].ToString();
                l.done = Convert.ToBoolean(dr["done"]);

                ll.Add(l);

            }
            return ll;

        }

        public List<Leased> getLeasedByBatteryID(Battery b)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp, done FROM Leased WHERE batteryID = " + b.ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Leased> ll = new List<Leased>();
            while (dr.Read())
            {
                Leased l = new Leased();
                l.ID = Convert.ToInt32(dr["ID"]);
                l.u = dbu.getUserByID(dr["userID"].ToString());
                l.b = dbb.getBattery(dr["batteryId"].ToString());
                l.timeStamp = dr["timeStamp"].ToString();
                l.done = Convert.ToBoolean(dr["done"]);

                ll.Add(l);

            }
            return ll;

        }

        public List<Leased> getLeasedByBatteryIDNotLeased(Battery b)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp, done FROM Leased WHERE batteryID = " + b.ID + "AND done = " + 0;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Leased> ll = new List<Leased>();
            while (dr.Read())
            {
                Leased l = new Leased();
                l.ID = Convert.ToInt32(dr["ID"]);
                l.u = dbu.getUserByID(dr["userID"].ToString());
                l.b = dbb.getBattery(dr["batteryId"].ToString());
                l.timeStamp = dr["timeStamp"].ToString();
                l.done = Convert.ToBoolean(dr["done"]);

                ll.Add(l);

            }
            return ll;

        }

        public List<Leased> getAllLeased()
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp, done FROM Leased";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Leased> ll = new List<Leased>();
            while (dr.Read())
            {
                Leased l = new Leased();
                l.ID = Convert.ToInt32(dr["ID"]);
                l.u = dbu.getUserByID(dr["userID"].ToString());
                l.b = dbb.getBattery(dr["batteryId"].ToString());
                l.timeStamp = dr["timeStamp"].ToString();
                l.done = Convert.ToBoolean(dr["done"]);
                
                ll.Add(l);

            }
            return ll;

        }

        public void updateLeased(Leased l)
        {
            SqlConnection con = dbc.GetConnection();
            String updateSql = "UPDATE Location " + "SET userID = @userID, batteryID = @batteryID, timeStamp = @timeStamp, done = @done " + "WHERE ID =" + l.ID;
            SqlCommand updateCmd = new SqlCommand(updateSql, con);


            updateCmd.Parameters.AddWithValue("@userID", l.u.ID);
            updateCmd.Parameters.AddWithValue("@batteryID", l.b.ID);
            updateCmd.Parameters.AddWithValue("@timeStamp", l.timeStamp);
            updateCmd.Parameters.AddWithValue("@done", l.done);
       


            try
            {


                updateCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error: " + ex.ToString());
            }


        }


    }
}
